package treballfinalUF2;

import java.io.UnsupportedEncodingException;
import java.sql.*;
import java.util.Properties;
import java.util.regex.Pattern;

import javax.mail.Message;
import javax.mail.MessagingException;
import javax.mail.NoSuchProviderException;
import javax.mail.Session;
import javax.mail.Transport;
import javax.mail.internet.AddressException;
import javax.mail.internet.InternetAddress;
import javax.mail.internet.MimeMessage;


public class operacionsAmbBBDD extends Connexio implements java.io.Serializable {
        String Missatge = null;
        String nom = null;
        String password = null;
        String id = null;
        String mail = null;
       
        public String getMail() {
                return mail;
        }
        public void setMail(String mail) {
                this.mail = mail;
        }
        public String getId() {
                return id;
        }
        public void setId(String id) {
                this.id = id;
        }
        public String getNom() {
                return nom;
        }
        public void setNom(String nom) {
                this.nom = nom;
        }
        public String getPassword() {
                return password;
        }
        public void setPassword(String password) {
                this.password = password;
        }
        /**
         *
         */
        private static final long serialVersionUID = 1L;
       
        //Connexio ObjBdades = new Connexio();
        public operacionsAmbBBDD() {
                super();
                // TODO Auto-generated constructor stub
        }
        public String escriuCorrectamentBBDD(String original) throws UnsupportedEncodingException{
        	
        	String titol=new String(original.getBytes("ISO-8859-1"),"UTF-8");
        	titol.replaceAll("\"", "``");
        	String newString = titol.replaceAll("\'", "`");
        	
        	System.out.println("------------>>>"+ newString);
        	
        	return newString;
        }
        public boolean chekEmpresaOK(String nomEmpresa, String passEmpresa) throws SQLException{   
        	
        	
        	boolean estat= true;
                establishConnection();
                        /**COMPROVAR SI EXISTEIX EMPRESA i està visible*/
                        int uno = 1;
                        String sqlComprovacio = "SELECT * FROM miBaseUF2FinalJSP.empresa WHERE nomempresa ='"+nomEmpresa+"' and "
                                        + "password='"+passEmpresa+"' and visible= '"+uno+"';  ";
                        System.out.println(sqlComprovacio);
                        PreparedStatement psCheck = getMiConexion().prepareStatement(sqlComprovacio);
                        ResultSet rsCheck = psCheck.executeQuery(sqlComprovacio);
                        while (rsCheck.next()) {
                        String id = rsCheck.getString(1);
                        }
                        rsCheck.last();
                        int rowCount = rsCheck.getRow();
                        System.out.println("Number of Rows=" + rowCount);
                        if(rowCount == 0)estat= false;
                       
                        closeMiConnexion();
                        
                        
                        
                return estat;
        }
       
        /*Inserta un alumne en una oferta sempre que no s'hagi inscrit abans*/
        public boolean insertaAlumneEmpresa(String idoferta,String grau,String idEmpresa, String mail ) throws SQLException{
                boolean result = true;
                String idalumne = null;
                establishConnection();
                System.out.println("*************"+mail);
                String sql1 ="Select idalumne from miBaseUF2FinalJSP.alumne where mail='"+mail+"';";
                PreparedStatement psCheck = getMiConexion().prepareStatement(sql1);
                ResultSet rsCheck = psCheck.executeQuery(sql1);
                while (rsCheck.next()) {
                idalumne = rsCheck.getString(1);
                }
                rsCheck.last();
                String sql = "INSERT INTO miBaseUF2FinalJSP.ofertaempresaalumne"
                                + "(idoferta, grau,idempresa,idalumne)" + " VALUES('" + idoferta
                                + "','" + grau+ "','"+idEmpresa +  "','" + idalumne+ "')";
                System.out.println(sql);
                PreparedStatement ps;
                try {
                        ps = getMiConexion().prepareStatement(sql);
                        ps.executeUpdate(sql);
                } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                        System.out.print("Creacio entrada ok" + result + ",  " +e);
                        result = false;
                }
               
               
               
                System.out.print("Creacio entrada ok" + result);
       
               
                closeMiConnexion();
                return result;
        }
         
        public boolean insertaOfertaAlumne(String mail,String titol,String nom, String descripcio ) throws SQLException{
            boolean result = true;
           
            establishConnection();
            System.out.println("*************"+mail);
           
            String sql = "INSERT INTO miBaseUF2FinalJSP.ofertalumne"
                            + "(mail, titol,nomalumne,descripcio)" + " VALUES('" + mail
                            + "','" + titol+ "','"+nom +  "','" + descripcio+ "')";
            System.out.println(sql);
            PreparedStatement ps;
            try {
                    ps = getMiConexion().prepareStatement(sql);
                    ps.executeUpdate(sql);
            } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                    System.out.print("Creacio entrada kko" + result + ",  " +e);
                    result = false;
            }
           
           
           
            System.out.print("Creacio entrada ok" + result);
   
           
            closeMiConnexion();
            return result;
    }
        
        
        
        
        
        
        /*Posar visible empreses*/      
        public void posaVisibleEmresa(String idEmpresa, String mail) throws SQLException {
                establishConnection();
                String sql = "UPDATE `miBaseUF2FinalJSP`.`empresa` SET `visible` = '1' WHERE `empresa`.`idempresa` ="+idEmpresa+";";
                PreparedStatement ps =  getMiConexion().prepareStatement(sql);
                ps.executeUpdate();
                closeMiConnexion();
               
                
                String host = "smtp.gmail.com";
                String to = mail;            				// s'ha de canviar això: camp TO
                String from = "victor522m@gmail.com";      // això també: camp FROM
                String pass = "Pon tu pass";             	 // i això: contrasenya de FROM
                String subject = "Assumpte";               // l'assumpte del missatge
                //important canviar la ip de alta
                String messageText = "La teva empresa ha estat donada d'alta";              // i el cos del missatge

                // Creem algunes propietats i obtenim la sessió

                Properties props = System.getProperties();
                props.put("mail.smtp.starttls.enable", "true");
                props.put("mail.smtp.host", host);
                props.put("mail.smtp.user", from);
                props.put("mail.smtp.password", pass);
                props.put("mail.smtp.port", "587"); 
                props.put("mail.smtp.auth", "true");
                Session mailSession = Session.getDefaultInstance(props, null);

                // Instanciem un nou MimeMessage i l'emplenem amb la info requerida

                Message msg = new MimeMessage(mailSession);
                try {
					msg.setFrom(new InternetAddress(from));
				} catch (AddressException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} catch (MessagingException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
                try {
					InternetAddress[] address = {new InternetAddress(to)};
				} catch (AddressException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
                try {
					msg.addRecipients(Message.RecipientType.TO, new InternetAddress[] { new InternetAddress(mail)});
				} catch (AddressException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} catch (MessagingException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
                try {
					msg.setSubject(subject);
				} catch (MessagingException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
                try {
					msg.setText(messageText);
				} catch (MessagingException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}

                // Passem el missatge al servei de transport encarregat

                Transport transport;
				try {
					transport = mailSession.getTransport("smtps");
					transport.connect(host, from, pass);
	                transport.sendMessage(msg, msg.getAllRecipients());
	                transport.close();
				} catch (NoSuchProviderException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} catch (MessagingException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
                

                // Algun missatge per pantalla		
                //Opcional
                System.out.println("El mail ha estat enviat a " + to);
                System.out.print(" des de " + from);
                System.out.print(" fent servir el host " + host + ".");
                //INSERT el usuari amb visible=false

        }
       
        /*Llista empreses*/
        public ResultSet retornaEmpreses() throws SQLException{
                establishConnection();          
                String sql = "SELECT * FROM miBaseUF2FinalJSP.empresa ";
                PreparedStatement ps =  getMiConexion().prepareStatement(sql);
                ps.executeQuery(sql);
                ResultSet result = ps.executeQuery(sql);        
               
                return result;
        }
        
       /*No la fem servir...*/
        public void posaVisibleAlumne(String idAlumne, String mail) throws SQLException{
                establishConnection();
                String sql = "UPDATE `miBaseUF2FinalJSP`.`alumne` SET `visible` = '1' WHERE `alumne`.`idalumne` ="+idAlumne+";";
                PreparedStatement ps =  getMiConexion().prepareStatement(sql);
                ps.executeUpdate();
                closeMiConnexion();
                String host = "smtp.gmail.com";
                String to = mail;            				// s'ha de canviar això: camp TO
                String from = "victor522m@gmail.com";      // això també: camp FROM
                String pass = "Pon tu pass";             	 // i això: contrasenya de FROM
                String subject = "Assumpte";               // l'assumpte del missatge
                //important canviar la ip de alta
                String messageText = "La teva empresa ha estat donada d'alta";              // i el cos del missatge

                // Creem algunes propietats i obtenim la sessió

                Properties props = System.getProperties();
                props.put("mail.smtp.starttls.enable", "true");
                props.put("mail.smtp.host", host);
                props.put("mail.smtp.user", from);
                props.put("mail.smtp.password", pass);
                props.put("mail.smtp.port", "587"); 
                props.put("mail.smtp.auth", "true");
                Session mailSession = Session.getDefaultInstance(props, null);

                // Instanciem un nou MimeMessage i l'emplenem amb la info requerida

                Message msg = new MimeMessage(mailSession);
                try {
					msg.setFrom(new InternetAddress(from));
				} catch (AddressException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} catch (MessagingException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
                try {
					InternetAddress[] address = {new InternetAddress(to)};
				} catch (AddressException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
                try {
					msg.addRecipients(Message.RecipientType.TO, new InternetAddress[] { new InternetAddress(mail)});
				} catch (AddressException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} catch (MessagingException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
                try {
					msg.setSubject(subject);
				} catch (MessagingException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
                try {
					msg.setText(messageText);
				} catch (MessagingException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}

                // Passem el missatge al servei de transport encarregat

                Transport transport;
				try {
					transport = mailSession.getTransport("smtps");
					transport.connect(host, from, pass);
	                transport.sendMessage(msg, msg.getAllRecipients());
	                transport.close();
				} catch (NoSuchProviderException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} catch (MessagingException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
                

                // Algun missatge per pantalla		
                //OPcional
                System.out.println("El mail ha estat enviat a" + to);
                System.out.println(" des de " + from);
                System.out.println(" fent servir el host " + host + ".");
                //insert el usuari amb visible=false

        }
       
        /*Llista alumnes*/
        public ResultSet retornaAlumne() throws SQLException{
                establishConnection();          
                String sql = "SELECT * FROM miBaseUF2FinalJSP.alumne ";
                PreparedStatement ps =  getMiConexion().prepareStatement(sql);
                ps.executeQuery(sql);
                ResultSet result = ps.executeQuery(sql);        
               
                return result;
        }
        /*Llista 1 alumne*/
        public ResultSet retornaAlumne(String idAlumne) throws SQLException{
            establishConnection();          
            String sql = "SELECT * FROM miBaseUF2FinalJSP.alumne where idalumne='"+idAlumne+"';";
            PreparedStatement ps =  getMiConexion().prepareStatement(sql);
            ps.executeQuery(sql);
            ResultSet result = ps.executeQuery(sql);        
           
            return result;
    }
       
        /*Llista ofertes*/
        public ResultSet retornarOferta() throws SQLException{
                establishConnection();          
                String sql = "SELECT * FROM miBaseUF2FinalJSP.oferta ";
                PreparedStatement ps =  getMiConexion().prepareStatement(sql);
                ps.executeQuery(sql);
                ResultSet result = ps.executeQuery(sql);        
               
                return result;
        }
        /*retornarOfertaEmpresaHistorica*/
        public ResultSet retornarOfertaEmpresaHistorica(String nomEmpresa) throws SQLException{
            establishConnection();          
            String sql = "SELECT * FROM miBaseUF2FinalJSP.ofertaHistorica where idempresa="+
            "(select idempresa from miBaseUF2FinalJSP.empresa where nomempresa='"+nomEmpresa+"');";
            PreparedStatement ps =  getMiConexion().prepareStatement(sql);
            ps.executeQuery(sql);
            ResultSet result = ps.executeQuery(sql);        
           
            return result;
    }
        /*Funcio per a llistar ofertes*/
        public ResultSet retornarOfertaEmpresa(String nomEmpresa) throws SQLException{
                establishConnection();          
                String sql = "SELECT * FROM miBaseUF2FinalJSP.oferta where idempresa="+
                "(select idempresa from miBaseUF2FinalJSP.empresa where nomempresa='"+nomEmpresa+"');";
                PreparedStatement ps =  getMiConexion().prepareStatement(sql);
                ps.executeQuery(sql);
                ResultSet result = ps.executeQuery(sql);        
               
                return result;
        }
       
        /*Retornar el total d'alumnes d'una oferta en concreta*/
        public int retornarTotalOfertaConcreta(String idoferta) {
                int totalOferta = 0;
                establishConnection();

                try {
                        String sqlComprovacio = "select count(idoferta), idoferta from miBaseUF2FinalJSP.ofertaempresaalumne where idoferta='"+idoferta+"' group by idoferta";
                        PreparedStatement psCheck = getMiConexion().prepareStatement(sqlComprovacio);
                        ResultSet rsCheck = psCheck.executeQuery(sqlComprovacio);
                        while (rsCheck.next()) {
                        String id = rsCheck.getString(1);
                        }
                        rsCheck.last();
                        totalOferta = rsCheck.getRow();
                        System.out.println("Number of Rows=" + totalOferta);
                }catch(Exception e){                            
                        System.out.print("Error en retonarTotalOferta()" + e);
                }
               
                closeMiConnexion();            
                return totalOferta;
        }
       
        public ResultSet retornarOfertaEspecificaEditar(String idOferta) throws SQLException {
                establishConnection();          
                String sql = "SELECT * FROM miBaseUF2FinalJSP.oferta where idoferta="+idOferta+";";
                PreparedStatement ps =  getMiConexion().prepareStatement(sql);
                ps.executeQuery(sql);
                ResultSet result = ps.executeQuery(sql);        
               
                return result;
        }
       
        public boolean editarOferta(String idoferta, String grau, String titoloferta, String descripcio, String fechafi) {
                boolean estatAccio = false;
                establishConnection();
                try{
                        String sqlEliminar = "UPDATE miBaseUF2FinalJSP.oferta SET grau='"+grau+"', titoloferta='"+titoloferta+"', descripcio='"+descripcio+"', fechafi='"+fechafi+"' WHERE idoferta="+idoferta+"";
                        PreparedStatement psCheck = getMiConexion().prepareStatement(sqlEliminar);                      
                        int result = psCheck.executeUpdate(sqlEliminar);
                       
                        if(result == 1) estatAccio = true;
                }catch(Exception e){    
                        System.out.print("Error al editar oferta" + e);
                }

                closeMiConnexion();
                return estatAccio;
        }
       
        public boolean eliminarOferta(String idoferta) {
                boolean estatAccio = false;
                establishConnection();
                try{
                        String sqlEliminar = "DELETE from miBaseUF2FinalJSP.oferta WHERE idoferta="+idoferta+"";
                        PreparedStatement psCheck = getMiConexion().prepareStatement(sqlEliminar);                      
                        int result = psCheck.executeUpdate(sqlEliminar);
                       
                        if(result == 1) estatAccio = true;
                }catch(Exception e){    
                        System.out.print("Error en eliminar oferta" + e);
                }

                closeMiConnexion();
                return estatAccio;
        }
       
        /*Insertem una empresa y comprovem que no s'hagi inscrit abans*/
        public boolean insertEmpresa(String nomEmpresa, String passEmpresa, String mailEmpresa,String descEmpresa) throws SQLException{
                establishConnection();
                boolean estat= false;
                try{
                        /**COMPROVAR SI EXISTEIX EMPRESA ABANS D'INSERTER-LA**/
                        String sqlComprovacio = "SELECT * FROM miBaseUF2FinalJSP.empresa WHERE nomempresa ='"+nomEmpresa+"'";
                        PreparedStatement psCheck = getMiConexion().prepareStatement(sqlComprovacio);
                        ResultSet rsCheck = psCheck.executeQuery(sqlComprovacio);
                        while (rsCheck.next()) {
                        String id = rsCheck.getString(1);
                        }
                        rsCheck.last();
                        int rowCount = rsCheck.getRow();
                        System.out.println("Number of Rows=" + rowCount);
                        if(rowCount == 0) {
                        /***/
                                String sql = "INSERT INTO miBaseUF2FinalJSP.empresa"
                                                + "(nomempresa, password,mail,descripcio,visible)" + " VALUES('" + nomEmpresa
                                                + "','" + passEmpresa+ "','"+mailEmpresa +  "','" + passEmpresa+ "','"+ 0 +"')";
                                PreparedStatement ps =  getMiConexion().prepareStatement(sql);
                               
                                ps.executeUpdate(sql);
                                estat = true;
                                System.out.print("Creacio empresa ok" + estat);
                        }
                        //Si la empresa ja existeix el ESTAT es retornar com a FALSE i avisara a l'usuari
                }catch(Exception e){    
                        System.out.print("Creacio empresa fail" + e);
                }

                closeMiConnexion();
                return estat;  
        }
       
        public boolean comprovaAlumneExist(String nomAlumne, String passAlumne, String mail){
                boolean result = true;
                establishConnection();
                try{
                        /**COMPROVAR SI EXISTEIX L'ALUMNE ABANS D'INSERTER-LA**/
                       
                        String sqlComprovacio = "SELECT * FROM miBaseUF2FinalJSP.alumne WHERE nomalumne ='"+nomAlumne+"' and password='"
                                        + passAlumne +"' and mail='"+ mail +"';";
                        System.out.println(sqlComprovacio);
                        PreparedStatement psCheck = getMiConexion().prepareStatement(sqlComprovacio);
                        ResultSet rsCheck = psCheck.executeQuery(sqlComprovacio);
                        int rowCount = 0;
                                        while (rsCheck.next()) {
                                        String id = rsCheck.getString(1);  
                                        }
                         rsCheck.last();
                     rowCount = rsCheck.getRow();
                     System.out.println("Number of Rows=" + rowCount);
                        if(rowCount == 0)result = false;
                        }catch(Exception e){                            
                                System.out.print("1233123123" + e);
                        }
                closeMiConnexion();
               
               
                return result;
        }
       
        public ResultSet retornaOferta(String mail) throws SQLException {
                establishConnection();          
                String sql = "SELECT * FROM miBaseUF2FinalJSP.oferta o, miBaseUF2FinalJSP.empresa e WHERE o.idempresa = e.idempresa and grau=" +
                		"(select grau from miBaseUF2FinalJSP.alumne where mail='"+mail+"')";
                PreparedStatement ps =  getMiConexion().prepareStatement(sql);
                ps.executeQuery(sql);
                ResultSet result = ps.executeQuery(sql);        
               
                return result;
        }
       
        public ResultSet retornarAlumneOferta(String idoferta) throws SQLException {
                establishConnection();          
                String sql = "SELECT o.idoferta, a.idalumne, a.nomalumne from miBaseUF2FinalJSP.alumne a, miBaseUF2FinalJSP.ofertaempresaalumne o where o.idoferta="+idoferta+" AND a.idalumne=o.idalumne";
                PreparedStatement ps =  getMiConexion().prepareStatement(sql);
                ps.executeQuery(sql);
                ResultSet result = ps.executeQuery(sql);        
               
                return result;
        }
       
        public boolean insertAlumne(String nomAlumne, String passAlumne, String mailAlumne,String grau) throws SQLException {
                establishConnection();
                boolean estat= false;
                try{
                        /**COMPROVAR SI EXISTEIX L'ALUMNE ABANS D'INSERTER-LA* el mail és únic*/
                        String sqlComprovacio = "SELECT * FROM miBaseUF2FinalJSP.alumne WHERE mail ='"+mailAlumne+"'";
                        PreparedStatement psCheck = getMiConexion().prepareStatement(sqlComprovacio);
                        ResultSet rsCheck = psCheck.executeQuery(sqlComprovacio);
                        while (rsCheck.next()) {
                        String id = rsCheck.getString(1);
                        }
                        rsCheck.last();
                        int rowCount = rsCheck.getRow();
                        System.out.println("Number of Rows=" + rowCount);
                        if(rowCount == 0) {
                        /***/if(!nomAlumne.equals("admin")){//no mola usuari alumne...
                                String sql = "INSERT INTO miBaseUF2FinalJSP.alumne"
                                                + "(nomalumne, password,mail,grau,visible)" + " VALUES('" + nomAlumne
                                                + "','" + passAlumne+ "','"+mailAlumne + "','"+ grau + "','"+ 0 +"')";
                                PreparedStatement ps =  getMiConexion().prepareStatement(sql);
                               
                                ps.executeUpdate(sql);
                                estat = true;
                                System.out.print("Creacio alumne ok  " + estat);
                        }
                        }
                }catch(Exception e){
                       
                        System.out.print("Creacio alumne fail  " + e);
                }

                closeMiConnexion();
                return estat;  
        }
       
        public boolean insertOferta(String nomempresa,String grau, String titoloferta, String descripcio, String fechainici, String fechafi) throws SQLException{
                establishConnection();
                String idempresa = null;
                String sql1 = "Select idempresa from miBaseUF2FinalJSP.empresa where nomempresa = '"+ nomempresa + "'";
                PreparedStatement psCheck = getMiConexion().prepareStatement(sql1);
                ResultSet rsCheck = psCheck.executeQuery(sql1);
                while (rsCheck.next()) {
               idempresa = rsCheck.getString(1);
                }
                System.out.println(nomempresa);
                System.out.println(idempresa);
               
                boolean estat= false;//FORMAT(Now(),'YYYY-MM-DD')
                try{
                        String sql = "INSERT INTO miBaseUF2FinalJSP.oferta"
                                        + "(idempresa,grau, titoloferta,descripcio, fechainici, fechafi)" + " VALUES('" + idempresa
                                        +"','" + grau + "','" + titoloferta + "','" + descripcio+ "','"+fechainici + "','"+ fechafi +"')";
                        PreparedStatement ps =  getMiConexion().prepareStatement(sql);
                               
                        ps.executeUpdate(sql);
                        estat = true;
                        System.out.print("Creacio oferta ok  " + estat);
                }catch(Exception e){
                        System.out.print("Creacio oferta fail  " + e);
                }

                closeMiConnexion();
                return estat;  
        }
       
        public boolean retornaOfertaAlumne(String idoferta,String mailAlumne,String password) throws SQLException{
                boolean estat = true;
                establishConnection();
                String sql =" SELECT count( * ) FROM miBaseUF2FinalJSP.ofertaempresaalumne " +
                                "WHERE idoferta = '"+idoferta+"' "
                                +"AND idalumne = ( SELECT idalumne FROM miBaseUF2FinalJSP.alumne " +
                                "WHERE mail = '"+mailAlumne+"' ) GROUP BY idoferta;";
                                PreparedStatement ps =  getMiConexion().prepareStatement(sql);
                                ResultSet rsCheck = ps.executeQuery(sql);
                                while (rsCheck.next()) {
                                String id = rsCheck.getString(1);
                                }
                                rsCheck.last();
                                int rowCount = rsCheck.getRow();
                                System.out.println("Number of Rows=" + rowCount);
                                if(rowCount >= 1)estat = false;
                                closeMiConnexion();
                return estat;
       
        }
       
        public ResultSet ofertesAlumneInscrit(String mail)throws SQLException{
                establishConnection();
       
                String sql = "SELECT o.idoferta,o.idempresa, oea.grau,o.titoloferta, o.descripcio, o.fechainici, o.fechafi, e.nomempresa" +
                                " FROM miBaseUF2FinalJSP.ofertaempresaalumne oea, " +
                                " miBaseUF2FinalJSP.empresa e, miBaseUF2FinalJSP.oferta o where   " +
                                " oea.idalumne=(select idalumne  FROM miBaseUF2FinalJSP.alumne where mail='"+mail+"'" +
                                                ") AND o.idoferta = oea.idoferta AND e.idempresa = oea.idempresa GROUP BY o.idoferta";
               
               
                /*SELECT o.idoferta, o.idempresa, oea.grau, o.titoloferta, o.descripcio, o.fechainici, o.fechafi, e.nomempresa
                FROM miBaseUF2FinalJSP.ofertaempresaalumne oea, miBaseUF2FinalJSP.empresa e, miBaseUF2FinalJSP.oferta o
                WHERE oea.idalumne = (
                SELECT idalumne
                FROM miBaseUF2FinalJSP.alumne
                WHERE mail = 'Fran@gmail.com' )
                AND o.idoferta = oea.idoferta
                AND e.idempresa = oea.idempresa
                GROUP BY oea.idoferta*/
               
               
                PreparedStatement ps =  getMiConexion().prepareStatement(sql);
                ps.executeQuery(sql);
                ResultSet result = ps.executeQuery(sql);        
               
                return result;
        }
       
        //Aqui es fa la comprovacio del correu
        public boolean checkmail(String correo) {
            Pattern pat = null;
            java.util.regex.Matcher mat = null;
            pat = Pattern.compile("^([0-9a-zA-Z]([_.w]*[0-9a-zA-Z])*@([0-9a-zA-Z][-w]*[0-9a-zA-Z].)+([a-zA-Z]{2,9}.)+[a-zA-Z]{2,3})$");
            mat = pat.matcher(correo);
            if (mat.find()) {
                System.out.println("[" + mat.group() + "]");
                return true;
            }else{
                return false;
            }
        }
       
        public String getMissatge() {
                return Missatge;
        }
       
        public void setMissatge(String missatge) {
                Missatge = missatge;
        }

}
